package com.whoimi.common.jdbc;

import jakarta.annotation.Resource;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import java.util.*;
import java.util.stream.Collectors;

/**
 * jdbc 操作符封装
 *
 * @author whoimi
 * @since 2025-08-01
 **/
@Component
public class JdbcQueryHelper {

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 基于SQL和查询对象构建分页查询
     */
    public <T> Page<T> buildWithSubquery(String baseSql, Object queryObj, Pageable pageable, RowMapper<T> rowMapper) {
        Map<String, QueryCondition> conditions = QueryParamParser.parseQueryParams(queryObj);
        QueryComponents components = buildQueryComponents(baseSql, conditions, pageable);
        List<T> content = jdbcTemplate.query(components.querySql, rowMapper, components.withPageParameters.toArray());
        Long total = Optional.ofNullable(jdbcTemplate.queryForObject(components.countSql, Long.class, components.parameters.toArray())).orElse(0L);
        return new PageImpl<>(content, pageable, total);
    }

    /**
     * 基于表名、SQL和查询对象构建分页查询
     *
     * @param tableName 表名
     * @param queryObj  查询对象
     * @param pageable  分页参数
     * @param rowMapper 结果映射器
     * @param <T>       返回类型
     * @return 分页结果
     */
    public <T> Page<T> buildPageWithTableName(String tableName, Object queryObj, Pageable pageable, RowMapper<T> rowMapper) {
        String baseSql = "SELECT * FROM " + tableName;
        return buildWithSubquery(baseSql, queryObj, pageable, rowMapper);
    }

    /**
     * 基于表名、自定义SELECT字段、查询对象构建分页查询
     *
     * @param tableName    表名
     * @param selectFields SELECT字段列表，如 "id, name, create_time"
     * @param queryObj     查询对象
     * @param pageable     分页参数
     * @param rowMapper    结果映射器
     * @param <T>          返回类型
     * @return 分页结果
     */
    public <T> Page<T> buildPageWithTableName(String tableName, String selectFields, Object queryObj, Pageable pageable, RowMapper<T> rowMapper) {
        String baseSql = "SELECT " + (selectFields != null && !selectFields.isEmpty() ? selectFields : "*") + " FROM " + tableName;
        return buildWithSubquery(baseSql, queryObj, pageable, rowMapper);
    }

    /**
     * 构建查询组件
     */
    private QueryComponents buildQueryComponents(String baseSql, Map<String, QueryCondition> conditions, Pageable pageable) {
        List<Object> parameters = new ArrayList<>();
        StringBuilder whereClause = buildWhereClause(conditions, parameters);
        StringBuilder querySql = new StringBuilder(baseSql);
        if (!whereClause.isEmpty()) {
            querySql.append(whereClause);
        }
        appendOrderByClause(querySql, pageable);
        List<Object> withPageParameters = new ArrayList<>(parameters);
        appendPaginationClause(querySql, pageable, withPageParameters);
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM (");
        StringBuilder countQuery = new StringBuilder(baseSql);
        if (!whereClause.isEmpty()) {
            countQuery.append(whereClause);
        }
        countSql.append(countQuery).append(") AS count_wrapper");
        return new QueryComponents(querySql.toString(), countSql.toString(), parameters, withPageParameters);
    }

    /**
     * 构建WHERE子句
     */
    private StringBuilder buildWhereClause(Map<String, QueryCondition> conditions, List<Object> parameters) {
        StringBuilder whereClause = new StringBuilder();
        if (conditions != null && !conditions.isEmpty()) {
            whereClause.append(" WHERE 1=1");
            for (QueryCondition condition : conditions.values()) {
                whereClause.append(" AND ");
                appendCondition(whereClause, condition, parameters);
            }
        }
        return whereClause;
    }

    /**
     * 添加查询条件
     */
    private void appendCondition(StringBuilder sql, QueryCondition condition, List<Object> parameters) {
        String fieldName = condition.getFieldName();
        SqlOperate.OperateEnum operate = condition.getOperate();
        Object value = condition.getValue();
        switch (operate) {
            case EQUAL:
                sql.append(fieldName).append(" = ?");
                parameters.add(value);
                break;

            case NOT_EQUAL:
                sql.append(fieldName).append(" != ?");
                parameters.add(value);
                break;

            case GREATER_THAN:
                sql.append(fieldName).append(" > ?");
                parameters.add(value);
                break;

            case GREATER_THAN_EQUAL:
                sql.append(fieldName).append(" >= ?");
                parameters.add(value);
                break;

            case LESS_THAN:
                sql.append(fieldName).append(" < ?");
                parameters.add(value);
                break;

            case LESS_THAN_EQUAL:
                sql.append(fieldName).append(" <= ?");
                parameters.add(value);
                break;
            case LIKE:
            case LIKE_LEFT:
            case LIKE_RIGHT:
                sql.append(fieldName).append(" LIKE ?");
                parameters.add(condition.formatValue());
                break;

            case IN:
                if (value instanceof Collection) {
                    @SuppressWarnings("unchecked")
                    Collection<Object> collection = (Collection<Object>) value;
                    if (!collection.isEmpty()) {
                        if (collection.size() == 1) {
                            sql.append(fieldName).append(" = ?");
                            parameters.add(collection.iterator().next());
                        } else {
                            sql.append(fieldName).append(" IN (");
                            int index = 0;
                            for (Object item : collection) {
                                sql.append("?");
                                if (index < collection.size() - 1) {
                                    sql.append(",");
                                }
                                parameters.add(item);
                                index++;
                            }
                            sql.append(")");
                        }
                    }
                } else if (value instanceof String) {
                    String[] values = value.toString().split(",");
                    List<String> validValues = Arrays.stream(values)
                            .map(String::trim)
                            .filter(s -> !s.isEmpty())
                            .toList();
                    if (!validValues.isEmpty()) {
                        if (validValues.size() == 1) {
                            sql.append(fieldName).append(" = ?");
                            parameters.add(validValues.get(0));
                        } else {
                            sql.append(fieldName).append(" IN (");
                            for (int i = 0; i < validValues.size(); i++) {
                                sql.append("?");
                                if (i < validValues.size() - 1) {
                                    sql.append(",");
                                }
                                parameters.add(validValues.get(i));
                            }
                            sql.append(")");
                        }
                    }
                }
                break;

            case NOT_IN:
                if (value instanceof Collection) {
                    @SuppressWarnings("unchecked") Collection<Object> collection = (Collection<Object>) value;
                    if (!collection.isEmpty()) {
                        if (collection.size() == 1) {
                            sql.append(fieldName).append(" != ?");
                            parameters.add(collection.iterator().next());
                        } else {
                            sql.append(fieldName).append(" NOT IN (");
                            int index = 0;
                            for (Object item : collection) {
                                sql.append("?");
                                if (index < collection.size() - 1) {
                                    sql.append(",");
                                }
                                parameters.add(item);
                                index++;
                            }
                            sql.append(")");
                        }
                    }
                } else if (value instanceof String) {
                    String[] values = value.toString().split(",");
                    List<String> validValues = Arrays.stream(values)
                            .map(String::trim)
                            .filter(s -> !s.isEmpty())
                            .toList();
                    if (!validValues.isEmpty()) {
                        if (validValues.size() == 1) {
                            sql.append(fieldName).append(" != ?");
                            parameters.add(validValues.get(0));
                        } else {
                            sql.append(fieldName).append(" NOT IN (");
                            for (int i = 0; i < validValues.size(); i++) {
                                sql.append("?");
                                if (i < validValues.size() - 1) {
                                    sql.append(",");
                                }
                                parameters.add(validValues.get(i));
                            }
                            sql.append(")");
                        }
                    }
                }
                break;

            case BETWEEN:
                if (value instanceof Collection) {
                    @SuppressWarnings("unchecked") Collection<Object> collection = (Collection<Object>) value;
                    if (collection.size() >= 2) {
                        sql.append(fieldName).append(" BETWEEN ? AND ?");
                        parameters.add(collection.toArray()[0]);
                        parameters.add(collection.toArray()[1]);
                    }
                } else if (value instanceof String) {
                    String[] values = value.toString().split(",");
                    if (values.length >= 2) {
                        List<String> validValues = Arrays.stream(values)
                                .map(String::trim)
                                .filter(s -> !s.isEmpty())
                                .toList();
                        if (validValues.size() >= 2) {
                            sql.append(fieldName).append(" BETWEEN ? AND ?");
                            parameters.add(validValues.get(0));
                            parameters.add(validValues.get(1));
                        }
                    }
                }
                break;

            case IS_NOT_NULL:
                sql.append(fieldName).append(" IS NOT NULL");
                break;

            case IS_NULL:
                sql.append(fieldName).append(" IS NULL");
                break;
        }
    }

    /**
     * 添加ORDER BY子句
     */
    private void appendOrderByClause(StringBuilder sql, Pageable pageable) {
        if (pageable.getSort().isSorted()) {
            sql.append(" ORDER BY ");
            String orderBy = pageable.getSort().stream().map(order -> {
                String property = order.getProperty();
                // 简单的SQL注入防护
                if (property.matches("^[a-zA-Z0-9_.]+$")) {
                    return property + " " + order.getDirection();
                }
                return null;
            }).filter(Objects::nonNull).collect(Collectors.joining(", "));

            if (!orderBy.isEmpty()) {
                sql.append(orderBy);
            }
        }
    }

    /**
     * 添加分页子句
     */
    private void appendPaginationClause(StringBuilder sql, Pageable pageable, List<Object> parameters) {
        if (!pageable.isUnpaged()) {
            sql.append(" LIMIT ? OFFSET ?");
            parameters.add(pageable.getPageSize());
            parameters.add(pageable.getPageSize() * pageable.getPageNumber());
        }
    }

    /**
         * 查询组件内部类
         */
        private record QueryComponents(String querySql, String countSql, List<Object> parameters,
                                       List<Object> withPageParameters) {
    }
}
